CREATE PROCEDURE [dbo].[EPMMedicationInfoSearch]
    @Type TINYINT,
    @Expression NVARCHAR(MAX)
AS
BEGIN
    SET NOCOUNT ON;
    IF @Type = 0
    BEGIN
        SELECT TOP (100)
               M.Nezam_No MedicalId,
               M.Name FirstName,
               M.Family LastName,
               S.Grade_Name SpecialtyName,
               CAST(S.Status AS SMALLINT) SpecialtyTypeCode,
               M.Grade_Code SpecialtyCode,
               CAST(ISNULL(M.[Status], 0) AS BIT) IsActive,
               [Description] = CASE
                                   WHEN ISNULL(M.Show_message, 0) <> 0
                                        AND LEN(ISNULL(M.Message, '')) > 0 THEN
                                       M.[Message]
                                   ELSE
                                       ''
                               END
        FROM dbo.Doctor M
            JOIN dbo.Grade S
                ON S.Grade_Code = M.Grade_Code
        WHERE M.Nezam_No = @Expression
        ORDER BY M.Nezam_No;
    END;
    ELSE IF LEN(ISNULL(@Expression, '')) > 0
    BEGIN
        SELECT TOP (100)
               M.Nezam_No MedicalId,
               M.Name FirstName,
               M.Family LastName,
               S.Grade_Name SpecialtyName,
               CAST(S.Status AS SMALLINT) SpecialtyTypeCode,
               M.Grade_Code SpecialtyCode,
               CAST(ISNULL(M.[Status], 0) AS BIT) IsActive,
               [Description] = CASE
                                   WHEN ISNULL(M.Show_message, 0) <> 0
                                        AND LEN(ISNULL(M.Message, '')) > 0 THEN
                                       M.[Message]
                                   ELSE
                                       ''
                               END
        FROM dbo.Doctor M
            JOIN dbo.Grade S
                ON S.Grade_Code = M.Grade_Code
        WHERE M.Nezam_No LIKE '%' + @Expression + '%'
              OR M.Family LIKE '%' + REPLACE(REPLACE(@Expression, NCHAR(1740), NCHAR(1610)), NCHAR(1705), NCHAR(1603))
                               + '%'
              OR M.Name LIKE '%' + REPLACE(REPLACE(@Expression, NCHAR(1740), NCHAR(1610)), NCHAR(1705), NCHAR(1603))
                             + '%'
        ORDER BY M.Nezam_No;
    END;
END;